﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace NewRecordApp
{
    public partial class Form1 : Form
    {
        private String m_file;
        private DataSet m_dsPayment;

  
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            openFileDialog1.DefaultExt = "xls";
            openFileDialog1.Filter = "Excel File 2000/2003 *.xls|*.xls|Excel 2007/2010 *.xlsx|*.xlsx";
            openFileDialog1.FileName = "";
            openFileDialog1.FilterIndex = 2; // ??
            DialogResult result = openFileDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
                if (Path.GetExtension(textBox1.Text) != ".xls" && 
                    Path.GetExtension(textBox1.Text) != ".xlsx")
                {
                    MessageBox.Show("Please select xls or xlsx file");
                }
                else
                {
                    m_file = textBox1.Text.Trim();
                }
            }
            else
            {
                return;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            NewRecordViewForm viewRecordsForm = new NewRecordViewForm();
            m_dsPayment = viewRecordsForm.getPaymentDataSet();
            viewRecordsForm.ShowDialog();
            return;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = m_dsPayment;
            dataGridView1.DataMember = m_dsPayment.Tables[0].TableName;
            
            String strConn = "Data Source=HEEFAN-PC\\SQLEXPRESS;" +
                    "Initial Catalog=TravelexForwardContract;" +
                    "Integrated Security=true";
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    conn.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                    {
                        bulkCopy.DestinationTableName = m_dsPayment.Tables[0].TableName;
                        try
                        {
                            bulkCopy.ColumnMappings.Add("Counterparty", "Counterparty");
                            bulkCopy.ColumnMappings.Add("Name", "ClientName");
                            bulkCopy.ColumnMappings.Add("Logical Number", "LogicalNumber");
                            bulkCopy.ColumnMappings.Add("Maturity Date", "MaturityDate");
                            bulkCopy.ColumnMappings.Add("Tot Amount", "TotalAmount");
                            bulkCopy.ColumnMappings.Add("Paid Status", "PaidStatus");
                            bulkCopy.ColumnMappings.Add("Localccy", "Localccy");
                            bulkCopy.ColumnMappings.Add("Local Amount", "LocalAmount");
                            bulkCopy.ColumnMappings.Add("Rate", "Rate");
                            bulkCopy.ColumnMappings.Add("Currency", "Currency");
                            bulkCopy.ColumnMappings.Add("Instrument", "Instrument");

                            bulkCopy.WriteToServer(m_dsPayment.Tables[0]);
                        }
                        catch (System.Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                            MessageBox.Show("You may have duplicated Logical Number Value, update database failed, please check your excel file");
                            return;
                        }
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    return;
                }

               

                conn.Close();
            }//end-of-using-conn
        }//end-of-function
    }//end-of-class
}
